Snowflakeでテストデータの生成に便利なGENERATORを試してみた #SnowflakeDB

※本エントリは、Snowflakeをより使いこなそう！ Advent Calendar 2021の7日目の記事となります。

さがらです。

Snowflakeでテストデータの生成に便利なGENERATOR関数を試してみたので、まとめてみます。

GENERATOR関数とは？

指定された行数、指定された生成期間（秒単位）、またはその両方に基づいてデータの行を作成できる関数です。

この記事では下記の公式Docに沿ってサンプルを試しつつ、どんなことが出来るのか見ていきたいと思います。

仕様説明

まず、GENERATOR関数を使うに当たり、いくつか他の関数と組み合わせて使う必要があります。下記のクエリと合わせてその仕様を説明します。

select seq4(), uniform(1, 10, random(12)) 
  from table(generator(rowcount => 10)) v 
  order by 1;

こちらのクエリを実行すると、下図のような結果を得ることが出来ます。

GENERATOR関数

GENERATOR関数ですが、ROWCOUNTやTIMELIMITといった引数と併せて使用します。

• ROWCOUNT：指定した数の行数分の結果を返す
• TIMELIMIT：指定した秒数の間、行を生成しそれを結果として返す

これらの引数を2つ同時に使用することも可能です。その時は、TIMELIMITで指定された秒数の間行を生成し続け、ROWCOUNTの値に達したらROWCOUNTの値分のレコードを返し、TIMELIMITで指定した値の秒数経過したらその時点までに生成されたレコードを返します。

TABLEリテラル

GENERATOR関数はFROM句の後にTABLEリテラルと併せて使うことが基本形です。

TABLEリテラルにGENERATOR関数のような行のセットを返すテーブル関数を引数に入れることで、各テーブル関数が返す結果に対してクエリ出来るようになります。

他にTABLEリテラルは、何かしらのセッション変数を用意して、参照するテーブルを動的に変化させたいときなどに使用することもできます。（詳しくは公式Docをご覧ください）

SEQ4関数

次は、SELECT句で使用されている関数の1つ目SEQ4関数について見ていきます。

このSEQ4関数は、単調に増加していく整数の羅列を返すことが出来ます。

引数には、「0」か「1」を入れることができ、「0」の場合は単調増加していきますが、「1」の場合は指定された整数幅に基づいて表現可能な最小の数を返します。 ※デフォルト値は0です。

SEQ関数の公式Docにもあるこちらの例がわかりやすいと思います。

他にも以下のように同様の関数が4種類あり、それぞれ仕様が異なっています。

• SEQ1：1バイトで表現可能な整数（-128～127）の間で整数値を返す
• SEQ2：2バイトで表現可能な整数（-32768～32767）の間で整数値を返す
• SEQ4：4バイトで表現可能な整数（-2147483648～2147483647）の間で整数値を返す
• SEQ8：8バイトで表現可能な整数（-9223372036854775807～9223372036854775807）の間で整数値を返す

UNIFORM関数

最後に、SELECT句で使用されている関数の2つ目UNIFORM関数について見ていきます。

このUNIFORM関数は、第1引数と第2引数で指定した値の間で、第3引数に指定した内容に基づき、乱数を生成して返します。第3引数にはRANDOM関数を指定することが多いと思います。

もし固定の値を返したい場合は、RANDOM関数の中に何かしらの値を入れて乱数シードを固定してあげるとよいと思います。

クエリサンプル

この説明だけだとわかりづらいと思いますので、いくつかクエリサンプルを載せておきたいと思います。

1つ目：UNIFORM関数の第3引数に固定の値を入れる

UNIFORM関数の第3引数に固定の値を入れると、UNIFORM関数はすべての行で同じ値を返します。

select seq4(), uniform(1, 10, 42) 
  from table(generator(rowcount => 10)) v 
  order by 1;

2つ目：UNIFORM関数で浮動小数点数を返す

下記の例のように、UNIFORM関数の第1引数と第2引数、いずれかに対してfloatでキャストしておくと、浮動小数点数の値を返します。

select uniform(0::float, 1::float, random()) from table(generator(rowcount => 5));

3つ目：完全に順序が確立して漏れのない数値の羅列を作る

SEQ4関数は単調に増加していく整数の羅列を返すのですが、返す行数が大量になると、途中で抜け漏れが発生する可能性があります。

そんな時は、下記のようにROW_NUMBER関数とWINDOW関数を合わせた書き方をすることで、単調増加する値の抜け漏れを防ぐことが出来ます。

select row_number() over (order by seq4()) 
    from table(generator(rowcount => 10));

いつ役にたつのか？

一番役に立つのは、何かしらのテストを実行したい場合だと思います。 簡単に数十億行レベルのデータを生成することが出来ますので、そのデータを用いてクエリを書いて動作速度を確認することが可能です。

また、連続した抜け漏れのない値も出力できますので、既存のテーブルと合わせてJOINすることも可能です。

ランダムな値を持つテーブルでちょっと試したい…そんな時に役立つのがGENERATOR関数ですね。

次回

Snowflakeをより使いこなそう！ Advent Calendar 2021、次回の8日目では、「Snowflakeのサンプルデータを一通り見てみる」というタイトルで執筆します。お楽しみに！